﻿using System;
using System.Text.RegularExpressions;

namespace PlsqlFormat
{
    public class Format
    {
        private int count;
        private SQLFormatter sqlfromat = new SQLFormatter();

        public string FomartSql(string sql)
        {
            count = 0;
            var arr = sql.Split('\n');
            var txt = "";
            bool IsExcep = false;
            bool whenMark = false;
            for (int i = 0; i < arr.Length; i++)
            {
                var line = arr[i].Trim();
                if (string.IsNullOrEmpty(line)) continue;
                if (line.StartsWithIgnoreCase("create "))
                {
                    var str = "";
                    while (!str.ContainsIgnoreCase(" as"))
                    {
                        str += line;
                        i++;
                        line = arr[i].Trim();
                    }
                    txt += FormatHead(str);
                    continue;
                }
                if (line.EqualsIgnoreCase("exception"))
                {
                    IsExcep = true;
                    whenMark = true;
                    count--;
                    txt = txt + WhiteSpace + line + "\n";
                    count++;
                    continue;
                }
                if (line.StartsWithIgnoreCase("select ")
                    || line.StartsWithIgnoreCase("update ")
                    || line.StartsWithIgnoreCase("insert ")
                    || line.StartsWithIgnoreCase("delete "))
                {
                    if (line.Contains(";") && line.Length > 100)
                    {
                        var sub = sqlfromat.Excute(line, count);
                        txt += sub;
                        //sub = sub.Replace("\t", "").Replace("\n", "").Replace(" ","").Trim();
                        //sub =sub.Substring(0,sub.IndexOf(";"));
                        //line = Regex.Replace(line, "//*.*?/*/", "").Replace(" ","").Trim();
                        //line= line.Substring(0, line.IndexOf(";"));
                        //if (!line.Equals(sub)&&!sub.Contains("CONCAT("))
                        //{
                        //    Console.WriteLine(line);
                        //    Console.WriteLine(sub);
                        //    Console.WriteLine("----------------------");
                        //}
                        continue;
                    }
                }
                if (line.EqualsIgnoreCase("begin")
                    || line.StartsWithIgnoreCase("if ")
                    || line.StartsWithIgnoreCase("while ")
                    || line.StartsWithIgnoreCase("for "))
                {
                    txt = txt + WhiteSpace + LenNewLine(line) + "\n";
                    count++;
                    continue;
                }
                if (line.EqualsIgnoreCase("loop"))
                {
                    var pre = arr[i - 1].Trim();
                    if (!pre.StartsWithIgnoreCase("for ") && !pre.StartsWithIgnoreCase("while "))
                    {
                        txt = txt + WhiteSpace + line + "\n";
                        count++;
                        continue;
                    }
                }
                if (line.StartsWithIgnoreCase("case "))
                {
                    txt = txt + WhiteSpace + line + "\n";
                    count++;
                    whenMark = true;
                    continue;
                }
                if (line.StartsWithIgnoreCase("when "))
                {
                    if (whenMark)
                    {
                        txt += WhiteSpace + line + "\n";
                        count++;
                        whenMark = false;
                        continue;
                    }
                    else
                    {
                        count--;
                        txt = txt + WhiteSpace + line + "\n";
                        count++;
                        continue;
                    }
                }
                if (line.StartsWithIgnoreCase("elsif ")
                    || line.EqualsIgnoreCase("else"))
                {
                    count--;
                    txt = txt + WhiteSpace + line + "\n";
                    count++;
                    continue;
                }
                if (line.StartsWithIgnoreCase("end ") ||
                    line.StartsWithIgnoreCase("end;"))
                {
                    var next = "";
                    int num = i;
                    while (num < arr.Length - 1)
                    {
                        next = arr[num + 1].Trim();
                        if (!string.IsNullOrEmpty(next)) break;
                        num++;
                    }
                    if (IsExcep)
                    {
                        count -= 2;
                        IsExcep = false;
                    }
                    else if (line.ContainsIgnoreCase("case"))
                    {
                        count -= 2;
                    }
                    else if (next.Equals("$$"))
                    {
                        count = 0;
                    }
                    else
                    {
                        count--;
                    }
                }
                if (line.Length > 180 && line.EndsWith(";"))
                {
                    txt += new SQLFormatter().LengthFomart(line, count);
                    continue;
                }
                txt += WhiteSpace + line + "\n";
            }
            return txt;
        }

        private string FormatHead(string str)
        {
            var head = str.Substring(0, str.IndexOf("(") + 1) + "\n";
            var param = Regex.Replace(str, ".*\\(", "");
            param = Regex.Replace(param, "\\).*", "");
            var arr = param.Split(',');
            for (int i = 0; i < arr.Length; i++)
            {
                var comma = i == arr.Length - 1 ? "\n" : ",\n";
                head += $"\t{arr[i].Trim()}{comma}";
            }
            head = head + str.Substring(str.IndexOf(")")) + "\n";
            return head;
        }

        private string LenNewLine(string line)
        {
            if (line.Length > 150)
            {
                if (line.StartsWithIgnoreCase("if"))
                {
                    int and = line.ContainsNumIgnoreCase(" and ");
                    int or = line.ContainsNumIgnoreCase(" or ");
                    if (and + or > 1)
                    {
                        var temp = line.ReplaceIgnoreCase("and ", "\n AND ").ReplaceIgnoreCase("or ", "\n OR ");
                        var arr = temp.Split('\n');
                        var str = "";
                        for (int i = 0; i < arr.Length; i++)
                        {
                            var item = arr[i].TrimStart();
                            var comma = i == arr.Length - 1 ? "" : "\n" + WhiteSpace;
                            while (item.Length < 120)
                            {
                                if (i < arr.Length - 1)
                                {
                                    item += arr[++i];

                                }
                                else
                                {
                                    break;
                                }
                            }
                            str += item + comma;
                        }
                        return str;
                    }
                }
            }
            return line;
        }

        private string WhiteSpace
        {
            get
            {
                var str = "";
                for (int i = 0; i < count; i++)
                {
                    str += "\t";
                }
                return str;
            }
        }
    }
}
